package com.ruoyi.web.controller.moon;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import com.github.houbb.heaven.util.lang.StringUtil;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.framework.config.ServerConfig;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.moon.domain.MoonBackups;
import com.ruoyi.moon.service.IMoonBackupsService;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.common.core.page.TableDataInfo;

/**
 * 备份Controller
 * 
 * @author admin
 * @date 2024-12-17
 */
@RestController
@RequestMapping("/moon/backups")
public class MoonBackupsController extends BaseController
{
    @Autowired
    private IMoonBackupsService moonBackupsService;

    /**
     * 查询备份列表
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:list')")
    @GetMapping("/list")
    public TableDataInfo list(MoonBackups moonBackups)
    {
        startPage();
        List<MoonBackups> list = moonBackupsService.selectMoonBackupsList(moonBackups);
        return getDataTable(list);
    }

    /**
     * 导出备份列表
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:export')")
    @Log(title = "备份", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, MoonBackups moonBackups)
    {
        List<MoonBackups> list = moonBackupsService.selectMoonBackupsList(moonBackups);
        ExcelUtil<MoonBackups> util = new ExcelUtil<MoonBackups>(MoonBackups.class);
        util.exportExcel(response, list, "备份数据");
    }

    /**
     * 获取备份详细信息
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:query')")
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id)
    {
        return success(moonBackupsService.selectMoonBackupsById(id));
    }

    /**
     * 新增备份
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:add')")
    @Log(title = "备份", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody MoonBackups moonBackups)
    {
        if(StringUtil.isEmpty(moonBackups.getName())){
            return AjaxResult.error("缺少名称");
        }
        // JDBC连接信息
        String url = "jdbc:mysql://39.101.137.224:3306/scoring_management?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
        String username = "liyezhongxin";
        String password = "Liyezhongxin123..";
        int i = 0;
        try {
            // 备份文件路径
            String backupFilePath = RuoYiConfig.getUploadPath();
            String newName = "/" + Instant.now().toEpochMilli()+moonBackups.getName()+".sql";
            backup(url, username, password, backupFilePath + newName);
            moonBackups.setBaseUrl("/profile/upload" + newName);
            moonBackups.setCreateBy(getUsername());
            i = moonBackupsService.insertMoonBackups(moonBackups);
        }catch (Exception e){
            e.printStackTrace();
        }
        return toAjax(i);
    }

    /**
     * 修改备份
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:edit')")
    @Log(title = "备份", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody MoonBackups moonBackups)
    {
        return toAjax(moonBackupsService.updateMoonBackups(moonBackups));
    }

    /**
     * 删除备份
     */
    @PreAuthorize("@ss.hasPermi('moon:backups:remove')")
    @Log(title = "备份", businessType = BusinessType.DELETE)
	@DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids)
    {
        return toAjax(moonBackupsService.deleteMoonBackupsByIds(ids));
    }


    public static void backup(String url, String username, String password, String backupFilePath) throws SQLException, IOException {
        // 建立数据库连接
        Connection con = DriverManager.getConnection(url, username, password);

        // 创建Statement对象，用于执行SQL查询
        Statement stmt = con.createStatement();

        // 查询所有表的表名
        ResultSet rsTables = stmt.executeQuery("SHOW TABLES");

        // 创建备份文件
        BufferedWriter writer = new BufferedWriter(new FileWriter(backupFilePath));

        ArrayList<String> tableNames = new ArrayList<>();
        while (rsTables.next()) {
            String tableName = rsTables.getString(1);
            tableNames.add(tableName);
        }

        // 遍历所有表并导出结构和数据
        for(int i=0;i<tableNames.size();i++){
            // 获取表结构的DDL语句
            ResultSet rsDDL = stmt.executeQuery("SHOW CREATE TABLE " + tableNames.get(i));
            if (rsDDL.next()) {
                String createTableSql = rsDDL.getString(2);
                writer.write(createTableSql + ";\n");
                System.out.println(createTableSql);
            }
            rsDDL.close();

            // 在获取表数据之前重新创建Statement
            Statement stmtData = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            stmtData.setFetchSize(Integer.MIN_VALUE);
            ResultSet rsData = stmtData.executeQuery("SELECT * FROM " + tableNames.get(i));

            // 将数据保存在内存中
            ResultSetMetaData meta = rsData.getMetaData();
            int columnCount = meta.getColumnCount();

            StringBuilder insertSql = new StringBuilder();
            while (rsData.next()) {
                insertSql.append("INSERT INTO ").append(tableNames.get(i)).append(" VALUES (");
                for (int j = 1; j <= columnCount; j++) {
                    Object value = rsData.getObject(j);
                    if (value != null) {
                        insertSql.append("'").append(value.toString().replace("'", "''")).append("'");
                    } else {
                        insertSql.append("NULL");
                    }
                    if (j < columnCount) {
                        insertSql.append(", ");
                    }
                }
                insertSql.append(");\n");

                // 写入备份文件
//                    writer.write(insertSql.toString());
//                    rsData.close();
//                    stmtData.close();
            }
//                System.out.println(insertSql);

            writer.write(insertSql.toString());
            rsData.close();
            stmtData.close();
        }


        // 关闭连接和相关资源
        rsTables.close();
        stmt.close();
        con.close();
        writer.close();
        System.out.println("Database backup successful.");
//        } catch (SQLException | IOException e) {
//            e.printStackTrace();
//        }
    }
}
